package database;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import entities.Style;
import exceptions.DBException;
import exceptions.MyException;
import exceptions.UserException;

/* ******************************************************** */
/* 						StyleHandler						*/
/*															*/
/*	Takes care of communicating with the database on		*/
/* 	behalf of styles										*/
/*															*/
/* ******************************************************** */

public class StyleHandler extends DBHandler {

	/*
	 * Add style - add style to db
	 */
	public void addStyle(InputStream is, String name, long length)
			throws MyException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			String query = "insert into " + Tables.Styles.dbName + "("
					+ Tables.Styles.Columns.NAME + ", "
					+ Tables.Styles.Columns.FILE + ") values (?, ?)";
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, name);
			pstmt.setBinaryStream(2, is, (int) length);
			pstmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			throw new MyException();
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	/*
	 * Remove style - remove style from db
	 */
	public void removeStyle(int styleId) throws MyException {
		// check styleId
		if (styleId < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the Session from the DB.
		String where = Tables.Styles.Columns.ID + "=" + styleId;
		ResultSet rs = null;
		try {
			rs = getStyleResultSet(where);
			if (rs.first()) {
				rs.deleteRow();
			}
		} catch (Exception e) {
			throw new MyException();
		} finally {
			closeConnection(rs);
		}
	}

	/*
	 * Remove style - remove style from db
	 */
	public void removeStyle(String name) throws MyException {
		// get the Session from the DB.
		String where = Tables.Styles.Columns.NAME + "= '" + name + "'";
		ResultSet rs = null;
		try {
			rs = getStyleResultSet(where);
			if (rs.first()) {
				rs.deleteRow();
			}
		} catch (Exception e) {
			throw new MyException();
		} finally {
			closeConnection(rs);
		}
	}

	/*
	 * Get style from db - with file
	 */
	public Style getStyle(int styleId) throws MyException {
		// check id
		if (styleId < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the Session from the DB.
		String where = Tables.Styles.Columns.ID + "= '" + styleId + "'";
		ResultSet rs = null;
		Style style = null;
		try {
			rs = getStyleResultSet(where);
			style = resultSetToStyle(rs, true, true);
		} finally {
			closeConnection(rs);
		}
		return style;
	}

	/*
	 * Get style from db - with file
	 */
	public Style getStyle(String name) throws MyException {
		// get the Session from the DB.
		String where = Tables.Styles.Columns.NAME + "= '" + name + "'";
		ResultSet rs = null;
		Style style = null;
		try {
			rs = getStyleResultSet(where);
			style = resultSetToStyle(rs, true, true);
		} finally {
			closeConnection(rs);
		}
		return style;
	}

	/*
	 * Get all styles info from db - without the file
	 */
	public List<Style> getAllStyle() throws MyException {
		ResultSet rs = null;
		List<Style> styles = new LinkedList<Style>();
		try {
			rs = getStyleResultSet(null);
			styles = buildStylesList(rs);
		} finally {
			closeConnection(rs);
		}
		return styles;
	}

	/* ******* */
	/* Helpers */
	/* ******* */

	/*
	 * Get Style ResultSet - fetch style ResultSet from the db
	 */
	private ResultSet getStyleResultSet(String where) throws DBException {
		ResultSet rs = null;
		try {
			rs = super.fetch(Tables.SCHEMA + "." + Tables.Styles.dbName,
					where, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (this.isEmptyResultSet(rs, Tables.SCHEMA + "."
				+ Tables.Session.dbName)) {
			this.closeConnection(rs);
			throw new DBException(Tables.Session.dbName, null,
					"Value not found", DBException.VALUE_NOT_FOUND);
		}
		return rs;
	}

	/*
	 * ResultSet to Style - converts ResultSet to Style
	 */
	private Style resultSetToStyle(ResultSet rs, boolean withFile,
			boolean needsNext) throws DBException {
		Style style = null;
		try {
			if (needsNext)
				rs.next();
			InputStream is = null;
			if (withFile) {
				Blob blob = rs.getBlob(Tables.Styles.Columns.FILE);
				is = blob.getBinaryStream();
			}
			style = new Style(rs.getInt(Tables.Styles.Columns.ID),
					rs.getString(Tables.Styles.Columns.NAME), is);

		} catch (SQLException e) {
			throw new DBException(Tables.Styles.dbName, null, e.getMessage(),
					DBException.CLOSE);
		}
		return style;
	}

	/*
	 * Build Styles List - build styles list from ResultSet
	 */
	private List<Style> buildStylesList(ResultSet rs) throws DBException {
		ArrayList<Style> styles = new ArrayList<Style>();

		// extract all items from ResultSet
		try {
			rs.first();
			while (!rs.isAfterLast()) {
				Style style = resultSetToStyle(rs, false, false);
				styles.add(style);
				rs.next();
			}
		} catch (SQLException e) {
			throw new DBException(Tables.Styles.dbName,
					"ON BUILD STYLES ARRAY", e.getMessage(),
					DBException.DEFAULT);
		} finally {
			this.closeConnection(rs);
		}
		return styles;
	}

}